﻿Imports AugmentLib.Database
Imports System.Configuration.ConfigurationManager
Imports AugmentLib.InputOutput.Encryption

Partial Class UploadValuation
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Session("StaffId") Is Nothing Then
            Response.Redirect("Default.aspx")
        ElseIf Session.Item("StaffType").ToString = "2" Then
            Response.Redirect("Default.aspx")
        End If

        If Not Page.IsPostBack Then

            lblStaffUID.Text = Session.Item("StaffId").ToString
            lblStaffType.Text = Session.Item("StaffType").ToString

            Dim strUpload As String = getLatestUpload()
            If strUpload <> String.Empty Then
                lblLastUploadTitle.Visible = True
                lblLastUpload.Text = strUpload
            End If
        End If
    End Sub

    Private Function getLatestUpload() As String
        Dim strDate As String = String.Empty
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT Distinct CreateDate FROM AW_Auction ORDER BY CreateDate DESC"
        objDB.FillData(TempDT, "AW_Auction", Database.SQLCommandType.NormalString)

        objDB.CloseConnection()
        If TempDT.Rows.Count > 0 Then
            strDate = Format(TempDT.Rows(0).Item("CreateDate"), "dd-MMM-yyyy")
        End If
        Return strDate
    End Function

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Try
            If fuUpload.PostedFile IsNot Nothing And fuUpload.HasFile Then
                lblStatus.Text = ExtractData(fuUpload.PostedFile)
            End If
        Catch ex As Exception
            lblStatus.Text = "Upload Not Success. Error message: " & ex.Message
        End Try
    End Sub

    Private Function ExtractData(ByVal PostedFile As System.Web.HttpPostedFile) As String
        Dim message As String = String.Empty
        'Dim strPath As String = "D:\LSK\File\"
        'Dim strPathMoved As String = "D:\LSK\File\Archive\" & DateTime.Now.ToString("ddMMyy_HHmmss")
        'Dim strLogPath As String = "D:\LSK\File\Log.txt"
        Dim strPath As String = "C:\REM_TEST\File\"
        Dim strPathMoved As String = "C:\REM_TEST\Archive\" & DateTime.Now.ToString("ddMMyy_HHmmss")
        Dim strLogPath As String = "C:\REM_TEST\File\Log.txt"

        Dim strFeedback As String = String.Empty

        'upload file then extract
        PostedFile.SaveAs(strPath & "AuctionEvalData.csv")

        Dim sw As System.IO.StreamWriter
        Dim oRead As System.IO.StreamReader
        Dim startRead As Boolean = False
        'Insert into Log - Begining
        sw = System.IO.File.AppendText(strLogPath)
        sw.WriteLine("------Begin------")
        sw.WriteLine(Now.ToString("ddMMyy HH:mm:ss"))

        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

        Try
            oRead = System.IO.File.OpenText(strPath & "AuctionEvalData.csv")

            If Not oRead Is Nothing Then
                objDB.OpenConnection()
                objDB.InitDbTrans()
                Do While oRead.Peek <> -1
                    objDB.ClearParameter()
                    Dim tmp As String = oRead.ReadLine()
                    If Not tmp = "" Then
                        'TODO : To regconize comma in text
                        Dim Cols() As String
                        tmp = tmp & ",,,,,,,,"
                        If InStr(tmp, """") < 1 Then
                            Cols = Split(tmp, ",")
                        Else
                            Dim tmpStr As String = ManipString(Mid(tmp, 1, tmp.Trim.Length), ",", "ڡ")
                            Cols = Split(tmpStr, ",")
                        End If

                        If Cols(0) = "1" Then
                            startRead = True
                        End If

                        If Cols(1) <> "" And startRead Then
                            'Insert into database
                            objDB.dbVariable.SQLString = "INSERT INTO AW_Auction(AucId, Daerah, Mukim, Sek, JenisLot, " & _
                                                        "NoLot, TkhPenilaian, LuasLot, UnitLuas, JenisHartanah, Alamat, Kawasan, " & _
                                                        "Pemberi, Penerima, Balasan, CreateDate, CreateBy, StatusDelete, " & _
                                                        "StatusLot,JenisHakmilik,NoHakmilik,JenisPegangan,TempohPajakan,TkhLuputPajakan,JenisBangunan," & _
                                                        "BilTktAtasTanah, BilTktBwhTanah, BilBilikTidur, LuasBgnUtama, JenisTanaman, NomborRss, Grid, Syer" & _
                                                        ") VALUES(@AucId, " & _
                                                        "@Daerah, @Mukim, @Sek1, @JenisLot, @NoLot, @TkhPenilaian, " & _
                                                        "@LuasLot, @UnitLuas, @JenisHartanah, @Alamat, @Kawasan, " & _
                                                        "@Pemberi, @Penerima, @Balasan, @CreateDate, @CreateBy, @StatusDelete," & _
                                                        "@StatusLot,@JenisHakmilik,@NoHakmilik,@JenisPegangan,@TempohPajakan,@TkhLuputPajakan,@JenisBangunan," & _
                                                        "@BilTktAtasTanah, @BilTktBwhTanah, @BilBilikTidur, @LuasBgnUtama, @JenisTanaman, @NomborRss, @Grid, @Syer" & _
                                                        ") "
                            objDB.AddParameter("AucId", Data.SqlDbType.UniqueIdentifier, Guid.NewGuid)
                            If Cols(1).Trim = "" Then
                                objDB.AddParameter("Daerah", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("Daerah", Data.SqlDbType.NVarChar, Replace(Cols(1).Trim, "ڡ", ","))
                            End If
                            If Cols(2).Trim = "" Then
                                objDB.AddParameter("Mukim", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("Mukim", Data.SqlDbType.NVarChar, Replace(Cols(2).Trim, "ڡ", ","))
                            End If
                            If Cols(3).Trim = "" Then
                                objDB.AddParameter("Sek1", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("Sek1", Data.SqlDbType.NVarChar, Replace(Cols(3).Trim, "ڡ", ","))
                            End If
                            If Cols(4).Trim = "" Then
                                objDB.AddParameter("JenisLot", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("JenisLot", Data.SqlDbType.NVarChar, Replace(Cols(4).Trim, "ڡ", ","))
                            End If
                            If Cols(5).Trim = "" Then
                                objDB.AddParameter("NoLot", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("NoLot", Data.SqlDbType.NVarChar, Replace(Cols(5).Trim, "ڡ", ","))
                            End If
                            If Cols(6).Trim = "" Then
                                objDB.AddParameter("StatusLot", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("StatusLot", Data.SqlDbType.NVarChar, Replace(Cols(6).Trim, "ڡ", ","))
                            End If
                            If Cols(7).Trim = "" Then
                                objDB.AddParameter("TkhPenilaian", Data.SqlDbType.DateTime, DBNull.Value)
                            Else
                                Dim tempstr As String = Cols(7).Trim
                                Dim dateCol() As String = Split(tempstr, "/")
                                If dateCol(0).Length = 1 Then dateCol(0) = "0" & dateCol(0)
                                If dateCol(1).Length = 1 Then dateCol(1) = "0" & dateCol(1)
                                If dateCol(2).Length = 1 Then dateCol(2) = "0" & dateCol(2)
                                If dateCol(2).Length = 2 Then dateCol(2) = "0" & dateCol(2)
                                tempstr = dateCol(0) & "/" & dateCol(1) & "/" & dateCol(2)
                                objDB.AddParameter("TkhPenilaian", Data.SqlDbType.DateTime, DateTime.ParseExact(tempstr, "dd/MM/yyyy", Nothing))
                            End If
                            If Cols(8).Trim = "" Then
                                objDB.AddParameter("LuasLot", Data.SqlDbType.Decimal, DBNull.Value)
                            Else
                                objDB.AddParameter("LuasLot", Data.SqlDbType.Decimal, CDbl(Replace(Cols(8).Trim, "ڡ", "").Trim))
                            End If
                            If Cols(9).Trim = "" Then
                                objDB.AddParameter("UnitLuas", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("UnitLuas", Data.SqlDbType.NVarChar, Replace(Cols(9).Trim, "ڡ", ","))
                            End If
                            If Cols(10).Trim = "" Then
                                objDB.AddParameter("JenisHakmilik", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("JenisHakmilik", Data.SqlDbType.NVarChar, Replace(Cols(10).Trim, "ڡ", ","))
                            End If
                            If Cols(11).Trim = "" Then
                                objDB.AddParameter("NoHakmilik", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("NoHakmilik", Data.SqlDbType.NVarChar, Replace(Cols(11).Trim, "ڡ", ","))
                            End If
                            If Cols(12).Trim = "" Then
                                objDB.AddParameter("JenisHartanah", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("JenisHartanah", Data.SqlDbType.NVarChar, Replace(Cols(12).Trim, "ڡ", ","))
                            End If
                            If Cols(13).Trim = "" Then
                                objDB.AddParameter("JenisPegangan", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("JenisPegangan", Data.SqlDbType.NVarChar, Replace(Cols(13).Trim, "ڡ", ","))
                            End If
                            If Cols(14).Trim = "" Then
                                objDB.AddParameter("TempohPajakan", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("TempohPajakan", Data.SqlDbType.NVarChar, Replace(Cols(14).Trim, "ڡ", ","))
                            End If
                            If Cols(15).Trim = "" Then  'Date
                                objDB.AddParameter("TkhLuputPajakan", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("TkhLuputPajakan", Data.SqlDbType.NVarChar, Replace(Cols(15).Trim, "ڡ", ","))
                            End If
                            If Cols(16).Trim = "" Then
                                objDB.AddParameter("JenisBangunan", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("JenisBangunan", Data.SqlDbType.NVarChar, Replace(Cols(16).Trim, "ڡ", ","))
                            End If
                            If Cols(17).Trim = "" Then
                                objDB.AddParameter("BilTktAtasTanah", Data.SqlDbType.Decimal, DBNull.Value)
                            Else
                                objDB.AddParameter("BilTktAtasTanah", Data.SqlDbType.Decimal, CDbl(Replace(Cols(17).Trim, "ڡ", "").Trim))
                            End If
                            If Cols(18).Trim = "" Then
                                objDB.AddParameter("BilTktBwhTanah", Data.SqlDbType.Decimal, DBNull.Value)
                            Else
                                objDB.AddParameter("BilTktBwhTanah", Data.SqlDbType.Decimal, CDbl(Replace(Cols(18).Trim, "ڡ", "").Trim))
                            End If
                            If Cols(19).Trim = "" Then
                                objDB.AddParameter("BilBilikTidur", Data.SqlDbType.Decimal, DBNull.Value)
                            Else
                                objDB.AddParameter("BilBilikTidur", Data.SqlDbType.Decimal, CDbl(Replace(Cols(19).Trim, "ڡ", "").Trim))
                            End If
                            If Cols(20).Trim = "" Then
                                objDB.AddParameter("LuasBgnUtama", Data.SqlDbType.Decimal, DBNull.Value)
                            Else
                                objDB.AddParameter("LuasBgnUtama", Data.SqlDbType.Decimal, CDbl(Replace(Cols(20).Trim, "ڡ", "").Trim))
                            End If
                            'JenisTanaman
                            If Cols(21).Trim = "" Then
                                objDB.AddParameter("JenisTanaman", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("JenisTanaman", Data.SqlDbType.NVarChar, Replace(Cols(21).Trim, "ڡ", ","))
                            End If

                            If Cols(22).Trim = "" Then
                                objDB.AddParameter("NomborRss", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("NomborRss", Data.SqlDbType.NVarChar, Replace(Cols(22).Trim, "ڡ", ","))
                            End If
                            If Cols(23).Trim = "" Then
                                objDB.AddParameter("Grid", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("Grid", Data.SqlDbType.NVarChar, Replace(Cols(23).Trim, "ڡ", ","))
                            End If
                            If Cols(24).Trim = "" Then
                                objDB.AddParameter("Alamat", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("Alamat", Data.SqlDbType.NVarChar, Replace(Cols(24).Trim, "ڡ", ","))
                            End If
                            If Cols(25).Trim = "" Then
                                objDB.AddParameter("Kawasan", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("Kawasan", Data.SqlDbType.NVarChar, Replace(Cols(25).Trim, "ڡ", ","))
                            End If
                            If Cols(26).Trim = "" Then
                                objDB.AddParameter("Syer", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("Syer", Data.SqlDbType.NVarChar, Replace(Cols(26).Trim, "ڡ", ","))
                            End If
                            If Cols(27).Trim = "" Then
                                objDB.AddParameter("Pemberi", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("Pemberi", Data.SqlDbType.NVarChar, Replace(Cols(27).Trim, "ڡ", ","))
                            End If
                            If Cols(28).Trim = "" Then
                                objDB.AddParameter("Penerima", Data.SqlDbType.NVarChar, DBNull.Value)
                            Else
                                objDB.AddParameter("Penerima", Data.SqlDbType.NVarChar, Replace(Cols(28).Trim, "ڡ", ","))
                            End If

                            If Cols(29).Trim = "" Then
                                objDB.AddParameter("Balasan", Data.SqlDbType.Decimal, DBNull.Value)
                            Else
                                objDB.AddParameter("Balasan", Data.SqlDbType.Decimal, CDbl(Replace(Cols(29).Trim, "ڡ", "").Trim))
                            End If
                            objDB.AddParameter("CreateDate", Data.SqlDbType.DateTime, Now())
                            objDB.AddParameter("CreateBy", Data.SqlDbType.UniqueIdentifier, New Guid(lblStaffUID.Text))
                            objDB.AddParameter("StatusDelete", Data.SqlDbType.TinyInt, 0)
                            objDB.ExecuteQuery()
                            'Insert into Log
                            If strFeedback = String.Empty Then
                                sw.WriteLine("Address :" & Cols(24).Trim)
                            Else
                                sw.WriteLine(strFeedback)
                            End If
                        End If
                    End If
                Loop
                objDB.DbTransCommit()
                objDB.CloseConnection()
            End If
            oRead.Close()
            oRead.Dispose()
            message = "Extraction Done! Please refer to log file for more information"

        Catch ex As Exception
            objDB.DbTransRollback()
            If objDB.dbVariable.objConn.State = Data.ConnectionState.Open Then
                objDB.CloseConnection()
            End If
            'Insert into Log
            sw.WriteLine(ex.Message)
            If Not oRead Is Nothing Then
                oRead.Close()
                oRead.Dispose()
            End If
            message = "Error. Please refer to log file for more information"
        Finally
            If Not oRead Is Nothing Then
                oRead.Close()
                oRead.Dispose()
            End If
            Dim dirTemp As New System.IO.DirectoryInfo(strPathMoved)
            Try
                System.IO.File.Move(strPath & "AuctionEvalData.csv", strPathMoved & "AuctionEvalData.csv")
                sw.WriteLine("Moved AuctionEvalData.csv as " & DateTime.Now.ToString("ddMMyy_HHmmss") & "AuctionEvalData.csv")
            Catch ex As Exception
                'Insert into Log
                sw.WriteLine(ex.Message)
            End Try

            'Insert into Log - Ending
            sw.WriteLine("------End------")
            sw.Flush()
            sw.Close()
        End Try

        Return message

    End Function



    Public Shared Function ManipString(ByVal TheValue As String, ByVal FindText As String, ByVal ReplaceText As String) As String

        'If text in between Indicator then replace FindText with ReplaceText
        '1. Find position of First Indicator
        '2. Continue looking for 2nd Position of Indicator

        Dim FirstPosition As Integer = 0
        Dim SecondPosition As Integer = 0
        Dim TheString As String = String.Empty
        Dim FinalResult As New System.Text.StringBuilder


        Try

            For nLoop As Integer = 1 To TheValue.Length

                If Mid(TheValue, nLoop, 1) = """" Then
                    FirstPosition = nLoop

                    For nInnerLoop As Integer = nLoop + 1 To TheValue.Length
                        If Mid(TheValue, nInnerLoop, 1) = """" Then
                            SecondPosition = nInnerLoop
                            Exit For
                        End If
                    Next


                    TheString = Mid(TheValue, FirstPosition + 1, SecondPosition - FirstPosition - 1)
                    TheString = TheString.Replace(FindText, ReplaceText)


                    FinalResult.Append(TheString & FindText)

                    nLoop = SecondPosition + 1
                Else
                    FinalResult.Append(Mid(TheValue, nLoop, 1))
                End If
            Next


        Catch ex As Exception

        End Try

        Return FinalResult.ToString

    End Function

End Class
